"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 系统管理函数-恢复控制函数（备机执行）
Case Name   : 函数pg_last_xact_replay_timestamp()，获取最后一个事务在恢复时重放的时间戳
Create At   : 2021-03-31
Owner       : opentestcase007
Description :
    1.主机新建测试表
    2.未进行任何事务，备机执行函数pg_last_xact_replay_timestamp()获取初始最新回放时间戳
    3.主机创建事务并rollback
    4.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳
    5.主机创建事务并commit
    6.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳
Expect      :
    1.主机新建测试表，创建成功
    2.未进行任何事务，备机执行函数pg_last_xact_replay_timestamp()获取初始最新回放时间戳，成功
    3.主机创建事务并rollback，成功
    4.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳，查询结果大于步骤2
    5.主机创建事务并commit，成功
    6.主备同步后，备机执行函数pg_last_xact_replay_timestamp()获取当前最新回放时间戳，查询结果大于步骤4
History     :
    2023/05/09 modify by opentestcase024: 优化整体用例，删除不必要且无效操作，验证具体获取事务replay时间戳正确
    2023/05/15 modify by opentestcase024: 获取时间转换format优化
"""

import os
import unittest
from datetime import datetime

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.tb_name = 't_innerfunc_system_manage_case0020'

    def test_func_sys_manage(self):
        step_txt = '----step1:主机新建测试表; expect:创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name} (sk integer);" \
            f"insert into {self.tb_name} values(1);"
        create_result = pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败' + step_txt)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, create_result,
                      '执行失败' + step_txt)

        step_txt = '----step2:未进行任何事务，备机执行函数pg_last_xact_replay_timestamp()' \
                   '获取初始最新回放时间戳; expect:成功----'
        self.log.info(step_txt)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp1 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertIsNotNone(timestamp1, '执行失败' + step_txt)

        step_txt = '----step3:主机创建事务并rollback; expect:成功----'
        self.log.info(step_txt)
        xact_sql = f'start transaction;' \
            f'insert into {self.tb_name} values(2);' \
            f'select * from {self.tb_name};' \
            f'rollback;' \
            f'select * from {self.tb_name};'
        xact_result = pri_sh.execut_db_sql(xact_sql)
        self.log.info(xact_result)
        self.assertIn('ROLLBACK', xact_result, '执行失败' + step_txt)

        step_txt = '----step4:主备同步后，备机执行函数pg_last_xact_replay_timestamp()' \
                   '获取当前最新回放时间戳; expect:查询结果大于步骤2----'
        self.log.info(step_txt)
        self.log.info('----等待主备同步----')
        self.sta1_sh.check_location_consistency('standby', node_num)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp2 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertGreater(timestamp2, timestamp1, '执行失败' + step_txt)

        step_txt = '----step5:主机创建事务并commit; expect:成功----'
        self.log.info(step_txt)
        xact_sql = f'start transaction;' \
            f'insert into {self.tb_name} values(2);' \
            f'select * from {self.tb_name};' \
            f'commit;'
        xact_result = pri_sh.execut_db_sql(xact_sql)
        self.log.info(xact_result)
        self.assertIn('COMMIT', xact_result, '执行失败' + step_txt)

        step_txt = '----step6:主备同步后，备机执行函数pg_last_xact_replay_timestamp()' \
                   '获取当前最新回放时间戳，查询结果大于步骤4----'
        self.log.info(step_txt)
        self.log.info('----等待主备同步----')
        self.sta1_sh.check_location_consistency('standby', node_num)
        check_sql = 'select pg_last_xact_replay_timestamp();'
        check_result = self.sta1_sh.execut_db_sql(check_sql)
        self.log.info(check_result)
        timestr = check_result.splitlines()[-2].strip()[0:-3]
        timestamp3 = datetime.strptime(timestr, '%Y-%m-%d %H:%M:%S.%f')
        self.assertGreater(timestamp3, timestamp2, '执行失败' + step_txt)

    def tearDown(self):
        self.log.info('----清理环境----')
        step_txt = '----删除表; expect:删除成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        drop_result = pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_result,
                      '执行失败' + step_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
